﻿Option Explicit On
Option Strict On
Imports System.Data.SqlClient

Public Class frmLocalidades

    Dim objLocalidad As New clsLocalidad
    Dim Operacion As String = ""
    Dim pais As String

    ' Cadena de conexión  
    Private Const cadenaconexion As String = "Data Source= .\SQLEXPRESS; Integrated Security=True; Initial Catalog=Ficticia"


    Private Sub frmLocalidad_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'enviar el control ComboBox y la consulta
        cargar_Combo(ComboP, "Select id_pais , NombrePais From Pais")
        cargar_Combo(ComboPro, "Select id_prov , NombreProvincia From Provincia")
        sub_HabilitarControles(False)
        sub_Refresh()
    End Sub

    Private Sub sub_Refresh()
        With objLocalidad
            If .fn_EjecutarQuery(clsLocalidad.TipoQuery.InfoTodo) Then
                Grid.DataSource = .dbDataSet.Tables("Localidad")

            Else
                MessageBox.Show(.pMsg)
            End If
        End With
    End Sub

    Private Sub sub_HabilitarControles(ByVal Valor As Boolean)
        pais = ""
        GrpListado.Enabled = Not Valor
        GrpIndividual.Enabled = Valor

        If GrpListado.Enabled Then
            Me.Height = 220
        Else
            Me.Height = 453
        End If

    End Sub
    Private Sub cargar_Combo(ByVal ComboBox As ComboBox, ByVal sql As String)
        ' nueva conexión indicando al SqlConnection la cadena de conexión  
        Dim cn As New SqlConnection(cadenaconexion)
        Try
            ' Abrir la conexión a Sql  
            cn.Open()

            ' Pasar la consulta sql y la conexión al Sql Command   
            Dim cmd As New SqlCommand(sql, cn)

            ' Inicializar un nuevo SqlDataAdapter   
            Dim da As New SqlDataAdapter(cmd)

            'Crear y Llenar un Dataset  
            Dim ds As New DataSet
            da.Fill(ds)

            ' asignar el DataSource al combobox  
            ComboBox.DataSource = ds.Tables(0)

            ' Asignar el campo a la propiedad DisplayMember del combo   
            ComboBox.DisplayMember = ds.Tables(0).Columns(1).Caption.ToString
            ComboBox.ValueMember = ds.Tables(0).Columns(0).Caption.ToString

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, _
                            "error", MessageBoxButtons.OK, _
                            MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
    End Sub

    Private Sub btCancelar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btCancelar.Click
        sub_HabilitarControles(False)
        btNuevo.Focus()
    End Sub

    Private Sub btEliminar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btEliminar.Click
        If Grid.RowCount <= 0 Then Exit Sub
        If (Grid.SelectedRows.Count >= 1) Then
            sub_HabilitarControles(True)
            Operacion = "Del"
            If MessageBox.Show("¿Esta seguro de eliminar la Provincia con Identificador:" & (Grid.SelectedRows(0).Cells("id_loc").Value.ToString), "Confirmación", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                btAceptar_Click(btAceptar, New System.EventArgs)
            End If
        Else
            MessageBox.Show("Seleccione una Fila, No una celda o columna")
            sub_HabilitarControles(False)
        End If
    End Sub


    Private Sub btAceptar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btAceptar.Click
        With objLocalidad
            Select Case Operacion
                Case "Add"
                    .pCiudad = Trim(Ciudad.Text)

                    If Trim(ComboP.Text) = "" Then
                        .pid_pais = 0
                    Else
                        .pid_pais = CInt(Trim(ComboP.SelectedValue.ToString))
                    End If

                    If Trim(ComboPro.Text) = "" Then
                        .pid_prov = 0
                    Else
                        .pid_prov = CInt(Trim(ComboPro.SelectedValue.ToString))
                    End If

                    If Not .fn_Existe(clsLocalidad.VerifExistType.Ambos) Then
                        If .fn_Add() Then
                            sub_Refresh()
                            sub_HabilitarControles(False)
                        Else
                            MessageBox.Show(.pMsg)
                        End If
                    Else
                        MessageBox.Show("El pais ya Existe.")
                    End If

                Case "Upd"
                    .pCiudad = Trim(Ciudad.Text)
                    .pid_loc = CInt(Trim(id_loc.Text))

                    If Trim(ComboP.Text) = "" Then
                        .pid_pais = 0
                    Else
                        .pid_pais = CInt(Trim(ComboP.SelectedValue.ToString))
                    End If

                    If Trim(ComboPro.Text) = "" Then
                        .pid_prov = 0
                    Else
                        .pid_prov = CInt(Trim(ComboPro.SelectedValue.ToString))
                    End If

                    If .fn_Upd() Then
                        sub_Refresh()
                        sub_HabilitarControles(False)

                    Else
                        MessageBox.Show(.pMsg)
                    End If

                Case "Del"
                    .pid_loc = CInt(Grid.SelectedRows(0).Cells("id_loc").Value.ToString)
                    .pid_prov = CInt(Grid.SelectedRows(0).Cells("id_prov").Value.ToString)
                    .pid_pais = CInt(Grid.SelectedRows(0).Cells("id_pais").Value.ToString)
                    If .fn_Del() Then
                        sub_Refresh()
                    Else
                        MessageBox.Show(.pMsg)

                    End If
            End Select
        End With
    End Sub

    Private Sub btNuevo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btNuevo.Click
        btAceptar.Text = "Guardar Nuevo"
        Operacion = "Add"
        sub_HabilitarControles(True)
        ComboP.Text = ""
        ComboPro.Text = ""
        Ciudad.Text = ""
        id_loc.Text = ""
        id_loc.ReadOnly = True
        Ciudad.Focus()

    End Sub

    Private Sub btModificar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btModificar.Click
        If Grid.RowCount <= 0 Then Exit Sub
        btAceptar.Text = "Guardar Modificación"
        Operacion = "Upd"

        With objLocalidad
            ' error cuadno no hay nada seleccionado o solo una columna
            'Dim aux As String
            'aux = Grid.SelectedRows(0)
            If (Grid.SelectedRows.Count = 1) Then
                sub_HabilitarControles(True)
                .pid_loc = CInt(Grid.SelectedRows(0).Cells("id_loc").Value.ToString)
                .pid_prov = CInt(Grid.SelectedRows(0).Cells("id_prov").Value.ToString)
                .pid_pais = CInt(Grid.SelectedRows(0).Cells("id_pais").Value.ToString)

                If .fn_EjecutarQuery(clsLocalidad.TipoQuery.InfoIndividual) Then
                    Ciudad.Text = .dbDataRow("Ciudad").ToString
                    ComboP.SelectedValue = .dbDataRow("id_pais").ToString
                    ComboPro.SelectedValue = .dbDataRow("id_prov").ToString
                    id_loc.Text = .dbDataRow("id_loc").ToString

                    id_loc.ReadOnly = True
                    Ciudad.Focus()
                Else
                    MessageBox.Show(.pMsg)

                End If
            Else
                MessageBox.Show("Seleccione la Fila, No una celda o columna")
                sub_HabilitarControles(False)
            End If


        End With
    End Sub

    Private Sub ComboP_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboP.SelectedIndexChanged

        If (Operacion = "Add") Or (Operacion = "Upd") Then
            pais = ComboP.SelectedValue.ToString
            cargar_Combo(ComboPro, "Select id_prov , NombreProvincia From Provincia Where id_pais=" & pais)
        End If
    End Sub

   
End Class